package com.fjxd.flightNumber.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fjxd.flightNumber.pojo.FlightNumber;
import com.fjxd.flightNumber.pojo.vo.HotCityVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author csw
 */
@Mapper
public interface FlightNumberMapper extends BaseMapper<FlightNumber> {

    /**
     * @param cityId1 输入起点城市Id
     * @param cityId2 输入终点城市Id
     * @return 返回航班号集合
     */
    @Select("select id from flight_number where up_city=#{city1} and down_city=#{city2}")
    List<Integer> getFlightNumberIdByCity(String city1, String city2);

    @Select("select id from flight_number where up_city_id=#{upCityId} and down_city_id = ANY(SELECT up_city_id from flight_number where down_city_id=#{downCityId}) limit #{startIndex},#{size}")
    List<Integer> getFlightNumberIdByUpCityId(Integer upCityId, Integer downCityId, Integer startIndex, Integer size);

    @Select("select * from flight_number where down_city_id=#{downCityId} and up_city_id =#{upCityId}")
    List<FlightNumber> getFlightNumberIdByDownCityId(Integer upCityId, Integer downCityId);

    @Select("select * from flight_number where down_city_id=#{downCity} and up_city_id = any(SELECT up_city_id from flight_number where down_city_id = #{downCityId})")
    List<FlightNumber> getFlightNumberByDownCity(Integer downCityId);

    @Select("select * from flight_number where up_city_id=#{upCityId} and down_city_id = ANY(SELECT up_city_id from flight_number where down_city_id=#{downCityId}) limit #{startIndex},#{size}")
    List<FlightNumber> getFlightNumberByUpCityIdAndDownCity(Integer upCityId, Integer downCityId, Integer startIndex, Integer size);

    @Select("select * from flight_number where up_city=#{upCity} and down_city=#{downCity}")
    List<FlightNumber>  getFlightNumberByUpCityAndDownCity(String upCity, String downCity);

    @Select("select count(up_city) as maxcity,up_city,a_id from flight_number where a_id=#{id} group by up_city order by maxcity desc limit 8;")
    List<HotCityVo> getHotCity(Integer id);

    @Select("select DISTINCT flight_number.* from flight_number where up_city=#{city}  and a_id=#{cityId} ORDER BY RAND() limit 8")
    List<FlightNumber> getTicketByCity(Integer cityId, String city);

    @Select("select * from flight_number where flight_number=#{flightNumber}")
    FlightNumber getFlightNumberByFlightNumber(String flightNumber);


}
